﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace VietKinsNutrition.Data
{
    public class ClsBaocaotonghopDAL
    {
        public DataTable SK_Nhanvien(int id)
        {
            string NamePrc = "SELECT TblSuckhoeNV.ThanghocId, TblSuckhoeNV.Id, TblSuckhoeNV.NhanviencongtacId, TblCongtacNV.Hovaten, TblCongtacNV.Gioitinh, TblSuckhoeNV.Ngaykiemtra, TblSuckhoeNV.Chieucao, TblSuckhoeNV.Mach, TblSuckhoeNV.Cannang, TblSuckhoeNV.Huyetap, TblSuckhoeNV.VongngucTB, TblSuckhoeNV.Nhietdo, TblSuckhoeNV.BMI, TblSuckhoeNV.Nhiptho, TblSuckhoeNV.Phanloaisk1, TblSuckhoeNV.Tuanhoan, TblSuckhoeNV.Phanloaisk2, TblSuckhoeNV.Hohap, TblSuckhoeNV.Phanloaisk3, TblSuckhoeNV.Tieuhoa, TblSuckhoeNV.Phanloaisk4, TblSuckhoeNV.Than, TblSuckhoeNV.Tietnieu, TblSuckhoeNV.Sinhduc, TblSuckhoeNV.Phanloaisk5, TblSuckhoeNV.Thankinh, TblSuckhoeNV.Phanloaisk6, TblSuckhoeNV.Tamthan, TblSuckhoeNV.Phanloaisk7, TblSuckhoeNV.Hevandong, TblSuckhoeNV.Phanloaisk8, TblSuckhoeNV.Noitiet, TblSuckhoeNV.Phanloaisk9, TblSuckhoeNV.Dalieu, TblSuckhoeNV.Phanloaisk10, TblSuckhoeNV.Sanphukhoa, TblSuckhoeNV.Phanloaisk11, TblSuckhoeNV.Matphaikhongkinh, TblSuckhoeNV.Mattraikhongkinh, TblSuckhoeNV.Matphaicokinh,TblSuckhoeNV.Mattraicokinh, TblSuckhoeNV.Cacbenhvemat, TblSuckhoeNV.Phanloaisk12, TblSuckhoeNV.Taitrainoithuong, TblSuckhoeNV.Taitrainoitham, TblSuckhoeNV.Taiphainoithuong, TblSuckhoeNV.Taiphainoitham, TblSuckhoeNV.Benhvetaimuihong, TblSuckhoeNV.Phanloaisk13, TblSuckhoeNV.Ranghammat, TblSuckhoeNV.Phanloaisk14, TblSuckhoeNV.SoluongHC, TblSuckhoeNV.Bachcau, TblSuckhoeNV.Tieucau, TblSuckhoeNV.Duongmau, TblSuckhoeNV.XNMauKhac, TblSuckhoeNV.Duong, TblSuckhoeNV.Protein, TblSuckhoeNV.XNNuoctieukhac, TblSuckhoeNV.Xquangphoi, TblSuckhoeNV.Ghicuhinhanh, TblSuckhoeNV.ChuandoanhinhanhKhac, TblSuckhoeNV.KL_Khoemanh, TblSuckhoeNV.KL_Macbenh, TblSuckhoeNV.KL_Tenbenh, TblSuckhoeNV.KL_Datskloai, TblSuckhoeNV.KL_Du_Khongdu, TblSuckhoeNV.KL_Huonggiaiquyet, TblCongtacNV.Id AS nvct, TblSuckhoeNV.Id FROM [SELECT TblNhanviencongtac.Id, (select top 1 Hotendem +' '+ Ten from TblNhanvien where Id =TblNhanviencongtac.NhanvienId ) AS  Hovaten,  (select top 1 Gioitinh from TblNhanvien where Id =TblNhanviencongtac.NhanvienId ) AS  Gioitinh,TblNhanviencongtac.NamhocId, TblNhanviencongtac.Motatrangthai  FROM TblNhanviencongtac]. AS TblCongtacNV INNER JOIN [SELECT TblKhamsuckhoeNV.ThanghocId,TblKhamsuckhoeNV.NhanviencongtacId, TblKhamsuckhoeNV.Id, TblKhamsuckhoeNV.Ngaykiemtra, TblKhamsuckhoeNV.Chieucao, TblKhamsuckhoeNV.Mach, TblKhamsuckhoeNV.Cannang, TblKhamsuckhoeNV.Huyetap, TblKhamsuckhoeNV.VongngucTB, TblKhamsuckhoeNV.Nhietdo, TblKhamsuckhoeNV.BMI, TblKhamsuckhoeNV.Nhiptho, TblKhamsuckhoeNV.Phanloaisk1, TblKhamsuckhoeNV.Tuanhoan, TblKhamsuckhoeNV.Phanloaisk2, TblKhamsuckhoeNV.Hohap, TblKhamsuckhoeNV.Phanloaisk3, TblKhamsuckhoeNV.Tieuhoa, TblKhamsuckhoeNV.Phanloaisk4, TblKhamsuckhoeNV.Than, TblKhamsuckhoeNV.Tietnieu, TblKhamsuckhoeNV.Sinhduc, TblKhamsuckhoeNV.Phanloaisk5, TblKhamsuckhoeNV.Thankinh, TblKhamsuckhoeNV.Phanloaisk6, TblKhamsuckhoeNV.Tamthan, TblKhamsuckhoeNV.Phanloaisk7, TblKhamsuckhoeNV.Hevandong, TblKhamsuckhoeNV.Phanloaisk8, TblKhamsuckhoeNV.Noitiet, TblKhamsuckhoeNV.Phanloaisk9, TblKhamsuckhoeNV.Dalieu, TblKhamsuckhoeNV.Phanloaisk10, TblKhamsuckhoeNV.Sanphukhoa, TblKhamsuckhoeNV.Phanloaisk11, TblKhamsuckhoeNV.Matphaikhongkinh, TblKhamsuckhoeNV.Mattraikhongkinh, TblKhamsuckhoeNV.Matphaicokinh, TblKhamsuckhoeNV.Mattraicokinh, TblKhamsuckhoeNV.Cacbenhvemat, TblKhamsuckhoeNV.Phanloaisk12, TblKhamsuckhoeNV.Taitrainoithuong, TblKhamsuckhoeNV.Taitrainoitham, TblKhamsuckhoeNV.Taiphainoithuong, TblKhamsuckhoeNV.Taiphainoitham, TblKhamsuckhoeNV.Benhvetaimuihong, TblKhamsuckhoeNV.Phanloaisk13, TblKhamsuckhoeNV.Ranghammat, TblKhamsuckhoeNV.Phanloaisk14, TblKhamsuckhoeNV.SoluongHC, TblKhamsuckhoeNV.Bachcau, TblKhamsuckhoeNV.Tieucau, TblKhamsuckhoeNV.Duongmau, TblKhamsuckhoeNV.XNMauKhac, TblKhamsuckhoeNV.Duong, TblKhamsuckhoeNV.Protein, TblKhamsuckhoeNV.XNNuoctieukhac, TblKhamsuckhoeNV.Xquangphoi, TblKhamsuckhoeNV.Ghicuhinhanh, TblKhamsuckhoeNV.ChuandoanhinhanhKhac, TblKhamsuckhoeNV.KL_Khoemanh, TblKhamsuckhoeNV.KL_Macbenh, TblKhamsuckhoeNV.KL_Tenbenh, TblKhamsuckhoeNV.KL_Datskloai, TblKhamsuckhoeNV.KL_Du_Khongdu, TblKhamsuckhoeNV.KL_Huonggiaiquyet FROM TblKhamsuckhoeNV ]. AS TblSuckhoeNV ON TblCongtacNV.Id = TblSuckhoeNV.NhanviencongtacId WHERE TblSuckhoeNV.Id = " + id;
            DataSet Dset = new DataSet();
            string NameTable = "SK_Nhanvien";
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);
            return NewTable;
        }

        public DataTable Tblbaocaothongke_Daunam_mau2(string Sql)
        {

            string NamePrc = Sql;
            DataSet Dset = new DataSet();
            string NameTable = "Tblbaocaothongke_Daunam_mau2";
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);
            return NewTable;
        }

        public DataTable bcdk(string thangtuoi, int thanghocid, int iBtn, string thangbc)
        {
            System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(ClsConnection.connectString);
            myConn.Open();
            System.Data.OleDb.OleDbDataAdapter commonAdapter = new System.Data.OleDb.OleDbDataAdapter();
            System.Data.OleDb.OleDbCommand commonCommand = new System.Data.OleDb.OleDbCommand();
            commonCommand.Connection = myConn;
            commonCommand.CommandType = CommandType.StoredProcedure;
            switch (iBtn)
            {
                case 2:
                    {
                        commonCommand.CommandText = "tkdaunammau1_2";
                        commonCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("thang", thangtuoi));
                        break;
                    }
                case 3:
                    {
                        commonCommand.CommandText = "tkdaunammau1_3";
                        commonCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("thang", thangtuoi));
                        break;
                    }
                case 4:
                    {
                        commonCommand.CommandText = "tkdaunammau1_4";
                        commonCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("thang", thangtuoi));
                        break;
                    }
                case 5:
                    {
                        commonCommand.CommandText = "tkdaunammau1_5";
                        commonCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("thangbc", thangbc));

                        commonCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("thangtuoi", thangtuoi));
                        break;
                    }
            }
            commonAdapter.SelectCommand = commonCommand;
            DataSet dtSet = new DataSet();
            try
            {
                commonAdapter.Fill(dtSet, "bcdk_2");
            }
            catch { }
            return dtSet.Tables[0];
        }

        public DataTable bcsl(int thanghocid, string thangbc)
        {
            System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(ClsConnection.connectString);
            myConn.Open();
            System.Data.OleDb.OleDbDataAdapter commonAdapter = new System.Data.OleDb.OleDbDataAdapter();
            System.Data.OleDb.OleDbCommand commonCommand = new System.Data.OleDb.OleDbCommand();
            commonCommand.Connection = myConn;
            commonCommand.CommandType = CommandType.StoredProcedure;
            commonCommand.CommandText = "Thongkesolieugiaoducdaunam4";
            commonCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("thangbc", thangbc));
            commonAdapter.SelectCommand = commonCommand;
            DataSet dtSet = new DataSet();
            try
            {
                commonAdapter.Fill(dtSet, "bcsl");
            }
            catch { }
            return dtSet.Tables[0];
        }
    }
}
